﻿using MODEL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DAL
{
   public class UserDAL
    {
        #region 三层架构原版

        /// <summary>
        /// 执行离线查询
        /// </summary>
        /// <param name="where"></param>
        /// <param name="pams"></param>
        /// <returns></returns>
        public static CurrenUser GetServiceAll(string sql, params SqlParameter[] pams)
        {
            DataTable table = DBhelper.SqlDataAdapter(sql, pams);
            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                CurrenUser user = new CurrenUser();
                user.UserID = Convert.ToInt32(row["UserID"]);
                user.UserName = Convert.ToString(row["UserName"]);
                user.Sex = row["Sex"].ToString() == "0" ? "男" : "女";
                user.Phone = Convert.ToString(row["Phone"]);
                user.Email = Convert.ToString(row["Email"]);
                user.RoleName = Convert.ToString(row["RoleName"]);
                return user;
            }
            return null;
        }

        /// <summary>
        /// 获取登录账户和密码
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwad"></param>
        /// <returns></returns>
        public static CurrenUser GetUserAll(string name, string pwad)
        {
            string sql = "select u.UserID, u.UserName,u.Sex,u.Phone,u.Email,r.RoleName from dbo.[User] as u inner join dbo.Role as r on u.FK_RoleID=r.RoleID where Account=@Account and Password=@Password";
            SqlParameter[] pams = {
                    new SqlParameter("@Account",name),
                    new SqlParameter("@Password",pwad)
                };
            return GetServiceAll(sql, pams);
        }

        /// <summary>
        /// 根据id返回结果
        /// </summary>
        /// <param name="Userid"></param>
        /// <returns></returns>
        public static CurrenUser GetIDAll(int Userid)
        {
            string sql = "select u.UserID, u.UserName,u.Sex,u.Phone,u.Email,r.RoleName from dbo.[User] as u inner join dbo.Role as r on u.FK_RoleID=r.RoleID where UserID=@UserID";
            SqlParameter[] pams = {
                    new SqlParameter("@UserID",Userid)
                };
            return GetServiceAll(sql, pams);
        }

        /// <summary>
        /// 删除语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetDeleteAll(CurrenUser sql)
        {
            string delete = "delete from dbo.[User] where UserID=@UserID";
            SqlParameter[] pams = {
                    new SqlParameter("@UserID",sql.UserID)
                };
            return DBhelper.ExecuteNonQuery(delete);
        }

        /// <summary>
        /// 修改语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int GetUpdateAll(CurrenUser sql)
        {
            string update = "update dbo.[User] set UserName=@UserName where UserID=@UserID";
            SqlParameter[] pams = {
                    new SqlParameter("@UserID",sql.UserID),
                    new SqlParameter("@UserName",sql.UserName)
                };
            return DBhelper.ExecuteNonQuery(update, pams);
        }

        #endregion

        #region 三层架构.连线式

        /// <summary>
        /// 根据账号和密码登录
        /// </summary>
        /// <param name="Account"></param>
        /// <param name="Password"></param>
        /// <returns></returns>
        public CurrenUser GetUserInfo(string Account, string Password)
        {
            string sql = "SELECT * FROM Role INNER JOIN [User] ON Role.RoleID = [User].FK_RoleID where Account=@Account and Password=@Password";
            //参数
            SqlParameter[] pams = {
                new SqlParameter("@Account",Account),
                new SqlParameter("@Password",Password)
            };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        CurrenUser user = new CurrenUser();
                        user.UserID = Convert.ToInt32(reader["UserID"]);
                        user.UserName = reader["UserName"].ToString();
                        user.Sex = Convert.ToInt32(reader["Sex"]) == 0 ? "男" : "女";
                        user.Phone = reader["Phone"].ToString();
                        user.Email = reader["Email"].ToString();
                        user.RoleName = reader["RoleName"].ToString();
                        return user;
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据id查询用户信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public CurrenUser GetUserByID(int userid)
        {
            string sql = "SELECT * FROM Role INNER JOIN [User] ON Role.RoleID = [User].FK_RoleID where UserID=@UserID";
            //参数
            SqlParameter[] pams = {
                new SqlParameter("@UserID",userid)
            };

            DataTable table = DBhelper.SqlDataAdapter(sql, pams);
            if (table.Rows.Count > 0)
            {
                DataRow row = table.Rows[0];
                CurrenUser user = new CurrenUser();
                user.UserID = Convert.ToInt32(row["UserID"]);
                user.UserName = Convert.ToString(row["UserName"]);
                user.Sex = row["Sex"].ToString() == "0" ? "男" : "女";
                user.Phone = Convert.ToString(row["Phone"]);
                user.Email = Convert.ToString(row["Email"]);
                user.RoleName = Convert.ToString(row["RoleName"]);
                return user;
            }
            return null;
        }
        #endregion

        #region 多条语句查询用户信息
        /// <summary>
        /// 多条件查询用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="phone"></param>
        /// <param name="sex"></param>
        /// <param name="startInTime"></param>
        /// <param name="EndInTime"></param>
        /// <returns></returns>
        public List<UserInfoQueryModel> GetUserList(UserInfoModel queryModel)
        {
            StringBuilder stringbuilder = new StringBuilder();
            stringbuilder.Append(@"
select u.UserID, u.UserName,
case u.Sex
when '0' then '男'
else '女' end as Sex ,
u.Account, u.Phone,u.Email,r.RoleName,u.CheckInTime,
CASE  u.IsDelete
when '1' then '是'
else '否' end as IsDelete,
u.AlterTime from dbo.[User] AS u INNER JOIN dbo.Role AS r on r.RoleID=u.FK_RoleID");

            List<string> listWhere = new List<string>();
            List<SqlParameter> listParm = new List<SqlParameter>();
            //姓名
            if (!string.IsNullOrWhiteSpace(queryModel.UserName))
            {
                listWhere.Add("u.UserName=@UserName");
                listParm.Add(new SqlParameter("@UserName", queryModel.UserName));
            }
            //电话
            if (!string.IsNullOrWhiteSpace(queryModel.Phone))
            {
                listWhere.Add("u.Phone=@Phone");
                listParm.Add(new SqlParameter("@Phone", queryModel.Phone));
            }
            //添加时间
            if (queryModel.start_CheckInTime != null)
            {
                listWhere.Add("u.CheckInTime>=@start_CheckInTime");
                listParm.Add(new SqlParameter("@start_CheckInTime", queryModel.start_CheckInTime));
            }
            if (queryModel.end_CheckInTime != null)
            {
                listWhere.Add("u.CheckInTime<=@end_CheckInTime");
                listParm.Add(new SqlParameter("@end_CheckInTime", queryModel.end_CheckInTime));
            }
            //修改时间
            if (queryModel.start_AlterTime != null)
            {
                listWhere.Add("u.AlterTime>=@start_AlterTime");
                listParm.Add(new SqlParameter("@start_AlterTime", queryModel.start_AlterTime));
            }
            if (queryModel.end_AlterTime != null)
            {
                listWhere.Add("u.AlterTime<=@end_AlterTime");
                listParm.Add(new SqlParameter("@end_AlterTime", queryModel.end_AlterTime));
            }
            //性别
            if (queryModel.Sex >= 0)
            {
                listWhere.Add("u.Sex=@Sex");
                listParm.Add(new SqlParameter("@Sex", queryModel.Sex));
            }
            //是否删除
            if (queryModel.IsDelete >= 0)
            {
                listWhere.Add("u.IsDelete=@IsDelete");
                listParm.Add(new SqlParameter("@IsDelete", queryModel.IsDelete));
            }
            //账号
            if (!string.IsNullOrWhiteSpace(queryModel.Account))
            {
                listWhere.Add("u.Account=@Account");
                listParm.Add(new SqlParameter("@Account", queryModel.Account));
            }
            //E-mail
            if (!string.IsNullOrWhiteSpace(queryModel.Email))
            {
                listWhere.Add("u.Email=@Email");
                listParm.Add(new SqlParameter("@Email", queryModel.Email));
            }
            //角色名称
            if (queryModel.RoleName != "==请选择==")
            {
                listWhere.Add("r.RoleName=@RoleName");
                listParm.Add(new SqlParameter("@RoleName", queryModel.RoleName));
            }

            if (listParm.Count > 0)
            {
                stringbuilder.Append($" where {string.Join(" and ", listWhere.ToArray())}");
                //sql += " where " + string.Join(" and ", listWhere.ToArray());
            }

            DataTable dt = DBhelper.SqlDataAdapter(stringbuilder.ToString(), listParm.ToArray());
            List<UserInfoQueryModel> list = new List<UserInfoQueryModel>();
            foreach (DataRow dr in dt.Rows)
            {
                UserInfoQueryModel userinfo = new UserInfoQueryModel()
                {
                    Account = dr["Account"].ToString(),
                    AlterTime = Convert.ToDateTime(dr["AlterTime"]),
                    CheckInTime = Convert.ToDateTime(dr["CheckInTime"]),
                    UserName = dr["UserName"].ToString(),
                    Sex = dr["Sex"].ToString(),
                    RoleName = dr["RoleName"].ToString(),
                    Phone = dr["Phone"].ToString(),
                    Email = dr["Email"].ToString(),
                    UserID = Convert.ToInt32(dr["UserID"]),
                    IsDelete = dr["IsDelete"].ToString(),
                };
                list.Add(userinfo);
            }
            return list;
        }
        #endregion

        #region 添加用户信息
        /// <summary>
        /// 添加用户
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Add(UserAdd sql)
        {
            string inser = @"insert into dbo.[User]
                           (UserName,Sex,Account,Password,Phone,Email,
                           FK_RoleID,CheckInTime,IsDelete,AlterTime) values
                           (@UserName, @Sex, @Account, @Password,
                          @Phone, @Email, @FK_RoleID, @CheckInTime, @IsDelete, @AlterTime)";
            SqlParameter[] parm ={
                new SqlParameter("@UserName",sql.UserName),
                new SqlParameter("@Sex",sql.Sex),
                new SqlParameter("@Account",sql.Account),
                new SqlParameter("@Password",sql.Password),
                new SqlParameter("@Phone",sql.Phone),
                new SqlParameter("@Email",sql.Email),
                new SqlParameter("@FK_RoleID",sql.FK_RoleID),
                new SqlParameter("@CheckInTime",DateTime.Now),
                new SqlParameter("@IsDelete",false),
                new SqlParameter("@AlterTime",DateTime.Now)
            };
            return DBhelper.ExecuteNonQuery(inser, parm);
        }
        #endregion

        #region 修改用户信息
        /// <summary>
        /// 根据id获取修改用户的信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public UpdateUserInfoModel UpdateUserModelID(int userid)
        {
            string sql = "select * from dbo.[User] where UserID=@UserID";
            SqlParameter[] pams = {
                new SqlParameter("@UserID",userid)
            };
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                if (reader.HasRows)
                {
                    if (reader.Read())
                    {
                        UpdateUserInfoModel model = new UpdateUserInfoModel()
                        {
                            UserID = Convert.ToInt32(reader["UserID"]),
                            UserName = reader["UserName"].ToString(),
                            Sex = Convert.ToInt32(reader["Sex"]),
                            Phone = reader["Phone"].ToString(),
                            Email = reader["Email"].ToString(),
                            Account = reader["Account"].ToString(),
                            FK_RoleID = Convert.ToInt32(reader["FK_RoleID"]),
                            IsDelete = Convert.ToBoolean(reader["IsDelete"])
                        };
                        return model;
                    }
                }
                return null;
            }
        }
        /// <summary>
        /// 修改用户信息
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Update(UpdateUserInfoModel sql)
        {
            string update = @"update dbo.[User] set  UserName=@UserName,Sex=@Sex, Account=@Account,
                                 FK_RoleID=@FK_RoleID, Phone=@Phone,Email=@Email
                                AlterTime=@AlterTime where UserID=@UserID";

            SqlParameter[] parm = {
                new SqlParameter("@UserID",sql.UserID),
                new SqlParameter("@UserName",sql.UserName),
                new SqlParameter("@Sex",sql.Sex),
                new SqlParameter("@Account",sql.Account),
                new SqlParameter("@FK_RoleID",sql.FK_RoleID),
                new SqlParameter("@Phone",sql.Phone),
                new SqlParameter("@Email",sql.Email),
                new SqlParameter("@AlterTime",sql.AlterTime)
            };
            return DBhelper.ExecuteNonQuery(update, parm);
        }
        #endregion

        #region 查询姓名，添加姓名不能重复
        /// <summary>
        /// 添加姓名不重复，如果大于0说明添加成功，否则添加失败
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool GetName(string name)
        {
            string sql = "select count(*) from dbo.[User] where UserName=@UserName";
            SqlParameter[] parm = {
                new SqlParameter("@UserName",name)
            };
            return Convert.ToInt32(DBhelper.ExecuteScalar(sql, parm)) > 0;

        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="delete"></param>
        /// <returns></returns>
        public int DeleteUser(int id)
        {
            //  string sql = @"update dbo.[User] set IsDelete=@IsDelete where UserID=@UserID";
            string sql = "delete from  dbo.[User] where UserID=@UserID";
            SqlParameter[] parm = {
                new SqlParameter("@IsDelete",true),
                new SqlParameter("UserID",id)
            };
            return DBhelper.ExecuteNonQuery(sql, parm);
        }
        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name = "sql" ></ param >
        /// < returns ></ returns >
        public int Updelete(int userid)
        {
            string ud = @"update dbo.[User] set IsDelete=1 where UserID=@UserID";
            SqlParameter[] parm = {
                new SqlParameter("@UserID",userid)
            };
            return DBhelper.ExecuteNonQuery(ud, parm);
        }
        /// <summary>
        /// 根据ID获取编辑用户信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public UpdateUserInfoModel GetdeleteDAL(int userid)
        {
            string sql = @"select * from [User] where UserID=@UserID";
            //参数
            SqlParameter[] pams = {
                new SqlParameter("@UserID",userid)

            };

            //连线式查询，调用DBhelper类中方法
            using (SqlDataReader reader = DBhelper.ExecuteReader(sql, pams))
            {
                //如果游标有数据行，说明根据账户名和密码查询到了数据，登录成功
                if (reader.HasRows)
                {
                    //登录成功后，执行下一条记录，获取登录用户的数据
                    if (reader.Read())
                    {
                        UpdateUserInfoModel user = new UpdateUserInfoModel();
                        user.UserID = Convert.ToInt32(reader["UserID"]);
                        user.UserName = reader["UserName"].ToString();
                        user.Sex = Convert.ToInt32(reader["Sex"]);
                        user.Phone = reader["Phone"].ToString();
                        user.Email = reader["Email"].ToString();
                        user.Account = reader["Account"].ToString();
                        user.FK_RoleID = Convert.ToInt32(reader["FK_RoleID"]);
                        user.IsDelete = Convert.ToBoolean(reader["IsDelete"]);
                        return user;
                    }
                }
                return null;
            }
        }
        #endregion

        #region 角色下拉框
        public DataTable juesedrop()
        {
            string sql = "select * from Role";
            return DBhelper.SqlDataAdapter(sql);
        }
        #endregion
    }
}
